package com.wodan.zhongjinjicang.common.orm.utils;

import java.util.List;
import java.util.Set;

import org.apache.commons.lang.StringUtils;
import org.springframework.util.Assert;

import com.google.common.collect.ImmutableSet;
import com.google.common.collect.Lists;

/**
 * sql语句生成器
 * 
 * @ClassName: SqlGenernateUtils
 * @author Administrator
 * @date 2015-11-4 下午8:34:29
 * @history
 * 
 */
public class SqlGenernateUtils {
	private static final String ESCAPE_CHAR = "`";

	private static final Set<String> SQL_KEY_WORDS;
	static {
		SQL_KEY_WORDS = ImmutableSet.of("desc", "status", "DESC", "STATUS");
	}

	/**
	 * 生成创建语句
	 * 
	 * @Description:
	 * @param tableName
	 * @param columns
	 * @return
	 */
	public static String createInsertSql(String tableName, String[] columns) {
		Assert.hasText(tableName);
		Assert.notEmpty(columns);

		String[] escapeColumns = escapeMysql(columns);

		StringBuilder sb = new StringBuilder();
		sb.append("INSERT INTO ").append(escapeMysql(tableName));
		sb.append(" (");
		sb.append(StringUtils.join(escapeColumns, ","));
		sb.append(") values (");
		sb.append(createInsertPlaceHolder(columns.length));
		sb.append(")");

		return sb.toString();
	}

	private static String[] escapeMysql(String[] columns) {
		ArrayBuilder<String> builder = new ArrayBuilder<>(String.class, columns.length);
		for (String origColumn : columns) {
			if (SQL_KEY_WORDS.contains(origColumn)) {
				String escapedColumn = escapeMysql(origColumn);
				builder.append(escapedColumn);
			} else {
				builder.append(origColumn);
			}
		}

		return builder.build();
	}

	/**
	 * 创建查询sql
	 * 
	 * @Description:
	 * @param tableName
	 * @param resultColumns
	 * @param conditionColumns
	 * @return
	 */
	public static String createQuerySql(String tableName, String[] resultColumns, String[] conditionColumns) {
		Assert.hasText(tableName);
		Assert.notEmpty(resultColumns);
		Assert.notEmpty(conditionColumns);

		String[] escapeResultColumns = escapeMysql(resultColumns);
		String[] escapeCondtionColums = escapeMysql(conditionColumns);

		List<String> conditions = appendEqualsAfterColumns(escapeCondtionColums);

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ").append(StringUtils.join(escapeResultColumns, ",")).append(" ");
		sb.append("FROM ").append(tableName).append(" ");
		sb.append("WHERE ").append(StringUtils.join(conditions, " and "));

		return sb.toString();
	}

	public static String createSelectByIdsSql(String tableName, String[] resultColumns, String idColumnName, int idCount) {
		Assert.hasText(tableName);
		Assert.notEmpty(resultColumns);
		Assert.hasText(idColumnName);
		Assert.isTrue(idCount > 0);

		String[] escapeResultColumns = escapeMysql(resultColumns);
		String inCondition = createInCondition(idColumnName, idCount);

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ").append(StringUtils.join(escapeResultColumns, ",")).append(" ");
		sb.append("FROM ").append(tableName).append(" ");
		sb.append("WHERE ").append(inCondition);
		
		return sb.toString();
	}
	
	public static String createSelectAllSql(String tableName, String[] resultColumns) {
		Assert.hasText(tableName);
		Assert.notEmpty(resultColumns);

		String[] escapeResultColumns = escapeMysql(resultColumns);

		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ").append(StringUtils.join(escapeResultColumns, ",")).append(" ");
		sb.append("FROM ").append(tableName);
		
		return sb.toString();
	}

	private static String createInCondition(String idColumnName, int idCount) {
		List<String> tempList = Lists.newArrayListWithCapacity(idCount);
		for (int i = 0; i < idCount; i++) {
			tempList.add("?");
		}

		String escapeCondtionColums = escapeMysql(new String[] { idColumnName })[0];
		StringBuilder sb = new StringBuilder();
		sb.append(escapeCondtionColums).append(" in(");
		sb.append(StringUtils.join(tempList, ", "));
		sb.append(") ");

		return sb.toString();
	}

	/**
	 * 生成update语句
	 * 
	 * @Description:
	 * @param tableName
	 * @param updateColumns
	 * @param conditionColumns
	 * @return
	 */
	public static String createUpdateSql(String tableName, String[] updateColumns, String[] conditionColumns) {
		Assert.hasText(tableName);
		Assert.notEmpty(updateColumns);
		Assert.notEmpty(conditionColumns);

		String[] escapeUpdateColumns = escapeMysql(updateColumns);
		String[] escapeConditionColumns = escapeMysql(conditionColumns);

		List<String> setColumns = appendEqualsAfterColumns(escapeUpdateColumns);
		List<String> conditions = appendEqualsAfterColumns(escapeConditionColumns);

		StringBuilder sb = new StringBuilder();
		sb.append("UPDATE ").append(tableName).append(" ");
		sb.append("SET ").append(StringUtils.join(setColumns, ",")).append(" ");
		sb.append("WHERE ").append(StringUtils.join(conditions, " and "));

		return sb.toString();
	}

	/**
	 * 生成删除的sql语句
	 * 
	 * @Description:
	 * @param tableName
	 * @param conditionColumns
	 * @return
	 */
	public static String createDeleteSql(String tableName, String[] conditionColumns) {
		Assert.hasText(tableName);
		Assert.notEmpty(conditionColumns);

		List<String> conditions = appendEqualsAfterColumns(conditionColumns);
		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM ").append(tableName).append(" ");
		sb.append("WHERE ").append(StringUtils.join(conditions, " and "));

		return sb.toString();
	}
	
	/**
	 * 生成删除的sql语句
	 * 
	 * @Description:
	 * @param tableName
	 * @param conditionColumns
	 * @return
	 */
	public static String createDeleteSql2(String tableName, String idColumnName) {
		Assert.hasText(tableName);
		Assert.hasText(idColumnName);

		StringBuilder sb = new StringBuilder();
		sb.append("DELETE FROM ").append(tableName).append(" ");
		sb.append("WHERE ").append(idColumnName).append("=?");

		return sb.toString();
	}

	private static List<String> appendEqualsAfterColumns(String[] conditionColumns) {
		Assert.notEmpty(conditionColumns);

		List<String> resultList = Lists.newArrayListWithCapacity(conditionColumns.length);
		for (String column : conditionColumns) {
			StringBuilder sb = new StringBuilder();
			sb.append(column).append("=").append("?");

			resultList.add(sb.toString());
		}

		return resultList;
	}

	/**
	 * 对于关键字进行转义
	 * 
	 * @Description:
	 * @param str
	 * @return
	 */
	private static String escapeMysql(String str) {
		return StringUtils.join(new Object[] { ESCAPE_CHAR, str, ESCAPE_CHAR });
	}

	/**
	 * 生成占位符
	 * 
	 * @Description:
	 * @param placeHolderSize
	 * @return
	 */
	private static String createInsertPlaceHolder(int placeHolderSize) {
		String[] resultArray = new String[placeHolderSize];
		for (int i = 0; i < placeHolderSize; i++) {
			resultArray[i] = "?";
		}

		return StringUtils.join(resultArray, ",");
	}
}
